package java6699.lesson11;

import java.sql.*;
import java.util.ArrayList;
import java.util.InputMismatchException;
import java.util.List;
import java.util.Scanner;

/**
 * @program: java_zzx
 * @description:
 * @author: zzx
 * @create: 2022-11-12 17:05
 **/
public class Select6699 {
    private static final String DRIVER;
    private static final String URL;
    private static final String USER;
    private static final String PW;

    private static final String DBNAME;

    static {
        //使用SQLite
        DRIVER = "com.mysql.jdbc.Driver";
        DBNAME = "db3";
        URL = "jdbc:mysql://localhost:3306/" + DBNAME;
        USER = "root";
        PW = "1234";
    }
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.print("请输入要查找的名字中包含的任何信息：");
        String strFind = sc.nextLine();
        if (strFind.trim().isEmpty()||strFind.matches("[0-9]+")){
            System.out.println("输入的信息为空或包含数字");
            return;
        }
        int maxId;
        try {
            System.out.print("请输入id范围：");
            maxId = sc.nextInt();
        }catch (InputMismatchException e){
            System.out.println("输入的不是数字");
            return;
        }
        if (maxId<=0){
            System.out.println("输入id不正确");
            return;
        }
        List<String> dateList = null;
        try {
            dateList = find6699(strFind, maxId);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (dateList==null){
            System.out.println("驱动未找到或查询出错");
        }else if (dateList.isEmpty()){
            System.out.println("没有查询到任何数据");
        }else {
            dateList.forEach(System.out::println);
        }    }
    public static List<String> find6699(String strFind, int maxId){
        List<String> list = new ArrayList<String>();
        try {
            Class.forName(DRIVER);//加载注册驱动
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //连接数据库
        try (Connection conn = DriverManager.getConnection(URL, USER, PW)) {
            String sql = "SELECT id,sno,sname from xsList where sname like '%' ? '%' and id< ? ";
            conn.setAutoCommit(false);
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,strFind);
            ps.setInt(2,maxId);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                int sno = resultSet.getInt("sno");
                String sname = resultSet.getString("sname");
                list.add(String.format("%d  ,%d,    %s",id,sno,sname));
            }
            resultSet.close();
            ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return list;
    }
}
